package ro.gateway.aida.obj.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import ro.gateway.aida.db.DBPersistenceManager;
import ro.gateway.aida.db.PersistenceToken;
import ro.gateway.aida.obj.AIDAActivityObject;
import ro.gateway.aida.obj.AIDAObjSector;
import ro.gateway.aida.obj.AIDASector;



/**
 * <p>Title: Romanian AIDA</p>
 * <p>Description: :D application</p>
 * <p>Copyright: Copyright (comparator) 2003</p>
 * <p>Company: Romania Development Gateway </p>
 * @author Mihai Popoaei, mihai_popoaei@yahoo.com, smike@intellisource.ro
 * @version 1.0-* @version $Id: AIDAObjSectorDB.java,v 1.1 2004/10/24 23:37:11 mihaipostelnicu Exp $
 */
public class AIDAObjSectorDB extends DBPersistenceManager
{
      private AIDAObjSectorDB( PersistenceToken token )
      {
            super( token );
      }

      public static AIDAObjSectorDB getManager( PersistenceToken token )
      {
            return new AIDAObjSectorDB( token );
      }

      /**
       * @deprecated
       * @param lang
       * @param activity_id
       * @return
       * @throws SQLException
       */
      public AIDAObjSector[] getSectorsForActByLang(
            String lang,
            long activity_id )
            throws SQLException
      {
            Connection con = getConnection();

            AIDAObjSector[] result = null;
            ArrayList items = new ArrayList();

            try
            {
                  PreparedStatement ps = con.prepareStatement( GET_FOR_ACT_BY_LANG );
                  ps.setLong( 1, activity_id );
                  ps.setString( 2, lang );
                  ResultSet rs = ps.executeQuery();
                  while ( rs.next() )
                  {
                        AIDAObjSector item = new AIDAObjSector();
                        AIDASector sector = new AIDASector();
                        item.setSector( sector );

                        item.setId( rs.getLong( 1 ) );
                        item.setOrigin( rs.getLong( 2 ) );
                        item.setOriginDbKey( rs.getString( 3 ) );
                        item.setDate_modif( rs.getDate( 4 ).getTime() );
                        item.setHarvest_date( rs.getDate( 5 ).getTime() );
                        sector.setRo_aida_code( rs.getInt( 6 ) );
                        sector.setDac5_code( rs.getString( 7 ) );
                        sector.setCrs_code( rs.getString( 8 ) );
                        sector.setParent_ro_aida_code( rs.getInt( 9 ) );
                        sector.setTitle( lang, rs.getString( 10 ) );

                        items.add( item );
                  }

                  rs.close();
                  ps.close();
            }
            catch ( SQLException sqlEx )
            {
                  throw sqlEx;
            }
            finally
            {
                  con.close();
            }

            if ( items.size() > 0 )
            {
                  result = new AIDAObjSector[ items.size() ];
                  items.toArray( result );
            }

            return result;
      }

      public void insert(
            AIDAObjSector item )
            throws SQLException
      {
            AIDAActivityObjectDB.getManager( token ).insert( db_token.getIdgenerators(), item );

            Connection con = getConnection();
            if ( item.getSector() == null )
            {
                  System.err.println( "incercare de inserv objSector cu sector null" );
                  return;
            }
            if ( item.getSector().getRo_aida_code() == -1 )
            {
            }

            try
            {
                  PreparedStatement ps = con.prepareStatement( INSERT );

                  ps.setLong( 1, item.getId() );
                  AIDASector subSector = item.getSubsector();
                  AIDASector subSector1 = item.getSubsector1();
                  AIDASector sector = item.getSector();
                  if ( subSector1 != null )
                  {
                        ps.setLong( 2, subSector1.getRo_aida_code() );
                  }
                  else if ( subSector != null )
                  {
                        ps.setLong( 2, subSector.getRo_aida_code() );
                  }
                  else
                  {
                        ps.setLong( 2, sector.getRo_aida_code() );
                  }
                  ps.executeUpdate();

                  ps.close();
            }
            catch ( SQLException sqlEx )
            {
                  throw sqlEx;
            }
            finally
            {
                  con.close();
            }
      }

      public AIDAObjSector[] getForActivity(
            long activity_id )
            throws SQLException
      {
            Connection con = getConnection();

            AIDAObjSector[] result = null;
            ArrayList items = new ArrayList();

            try
            {
                  PreparedStatement ps = con.prepareStatement( GET_FOR_ACT );
                  ps.setLong( 1, activity_id );
                  ResultSet rs = ps.executeQuery();
                  while ( rs.next() )
                  {
                        AIDAObjSector item = new AIDAObjSector();
                        item.setId( rs.getLong( 1 ) );
                        int ro_aida_code = rs.getInt( 2 );
                        if ( ( ro_aida_code == -1 ) || ( rs.wasNull() ) )
                        {
                              item.setSector( null );
                              item.setSubsector( null );
                        }
                        else
                        {
                              AIDASector sub_sector = AIDASectorPersMan.
                                    getManager( db_token ).
                                    getById( ro_aida_code );
                              if ( sub_sector.getParent_ro_aida_code() == -1 )
                              {
                                    item.setSector( sub_sector );
                                    item.setSubsector( null );
                              }
                              else
                              {
                                    item.setSubsector( sub_sector );
                                    item.setSector( AIDASectorPersMan.
                                          getManager( db_token ).
                                          getById( sub_sector.getParent_ro_aida_code() ) );
                              }

                              if ( item.getSector() != null && item.getSector().getParent_ro_aida_code() != -1 )
                              {
                                    AIDASector _sector = AIDASectorPersMan.
                                          getManager( db_token ).
                                          getById( item.getSector().getParent_ro_aida_code() );
                                    if ( _sector != null )
                                    {
                                          item.setSubsector1( item.getSubsector() );
                                          item.setSubsector( item.getSector() );
                                          item.setSector( _sector );
                                    }
                              }
                        }
                        items.add( item );
                  }

                  rs.close();
                  ps.close();
            }
            catch ( SQLException sqlEx )
            {
                  throw sqlEx;
            }
            finally
            {
                  con.close();
            }

            if ( items.size() > 0 )
            {
                  result = new AIDAObjSector[ items.size() ];
                  items.toArray( result );
            }

            return result;
      }

      public final void delete(
            long id )
            throws SQLException
      {
            Connection con = getConnection();

            try
            {
                  PreparedStatement ps = con.prepareStatement( DELETE );
                  ps.setLong( 1, id );
                  ps.executeUpdate();
                  ps.close();
            }
            catch ( SQLException sqlEx )
            {
                  throw sqlEx;
            }
            finally
            {
                  con.close();
            }

      }

      public final void delete_for_activity(
            long id )
            throws SQLException
      {
            Connection con = getConnection();

            String database_pname = con.getMetaData().getDatabaseProductName();
            if ( 0 == database_pname.compareToIgnoreCase( "MYSQL" ) )
            {
                  System.out.println( "MYSQL - deleteForActivity (documents) -- DEBUG" );
                  con.close();
                  deleteForActivityMYSQL( id );
                  return;
            }

            try
            {
                  PreparedStatement ps = con.prepareStatement( DELETE_FOR_ACTIVITY );
                  ps.setLong( 1, id );
                  ps.executeUpdate();
                  ps.close();

                  AIDAActivityObjectDB.getManager( token ).deleteForActivityByType(
                        con, id, AIDAActivityObject.TYPE_SECTOR );
            }
            catch ( SQLException sqlEx )
            {
                  throw sqlEx;
            }
            finally
            {
                  con.close();
            }

      }


      private void deleteForActivityMYSQL(
            long id )
            throws SQLException
      {

            AIDAObjSector[] sectors = getForActivity( id );

            if ( sectors != null )
            {
                  Connection con = getConnection();
                  try
                  {
                        PreparedStatement ps = con.prepareStatement(
                              "DELETE FROM sectors WHERE id = ?" );
                        for ( int i = 0; i < sectors.length; i++ )
                        {
                              ps.clearParameters();
                              ps.setLong( 1, sectors[ i ].getId() );
                              ps.executeUpdate();
                        }
                        ps.close();

                        AIDAActivityObjectDB.getManager( token ).deleteForActivityByType(
                              con, id, AIDAActivityObject.TYPE_SECTOR );
                  }
                  catch ( SQLException sqlEx )
                  {
                        throw sqlEx;
                  }
                  finally
                  {
                        con.close();
                  }
            }
      }


      private static final String GET_FOR_ACT_BY_LANG =
            "SELECT o.id, o.origin, o.origin_dbk, o.date_modif, o.harvest_date, " +
            "ras.ro_aida_code, ras.dac5_code, ras.crs_code, ras.parent_ro_aida_code, " +
            "ras.name " +
            "FROM objects o, sectors s, ro_aida_sectors ras " +
            "WHERE o.id=s.id AND s.ro_aida_code = ras.ro_aida_code " +
            "AND o.activity_id = ? AND ras.lang = ?";
      private static final String GET_FOR_ACT =
            "SELECT s.id, s.ro_aida_code " +
            "FROM sectors s, objects o " +
            "WHERE s.id = o.id AND o.activity_id=?";
      private static final String INSERT =
            "INSERT INTO sectors (id, ro_aida_code) " +
            "VALUES (?, ?)";
      private static final String DELETE =
            "DELETE FROM sectors WHERE id = ?";
      private static final String DELETE_FOR_ACTIVITY =
            "DELETE FROM sectors WHERE id IN (SELECT id FROM objects WHERE activity_id=?)";

}